package com.classbook.violet.baigiang.database;

import java.io.File;
import java.io.FilenameFilter;
import java.util.ArrayList;
import java.util.List;

import com.classbook.violet.baigiang.subject.LessonNode;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;


public class DatabaseHelper extends SQLiteOpenHelper{
	
	private static final String TABLE_RECENT = "recent";
	
	private static final String KEY_RECENT_ID = "id";
	private static final String KEY_RECENT_TITLE = "title";
	private static final String KEY_RECENT_PATH = "path";
	
	private static final String TABLE_CATEGORY = "category";
	
	private static final String KEY_CATEGORY_NAME = "name";
	private static final String KEY_CATEGORY_TITLE = "tile";
	private static final String KEY_CATEGORY_PATH = "PATH";
	
	private Context mContext;
	
	public DatabaseHelper(Context context) {
		super(context,"BookDatabase",null,1);
		// TODO Auto-generated constructor stub
		mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String CREAT_RECENT = "CREATE TABLE " + TABLE_RECENT + "("
				+ KEY_RECENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ KEY_RECENT_TITLE + " TEXT,"
				+ KEY_RECENT_PATH + " TEXT)";
		db.execSQL(CREAT_RECENT);
		
		String CREAT_CATEGORY = "CREATE TABLE " + TABLE_CATEGORY + "("
				+ KEY_CATEGORY_NAME + " TEXT,"
				+ KEY_CATEGORY_TITLE + " TEXT,"
				+ KEY_CATEGORY_PATH + " TEXT )";
		db.execSQL(CREAT_CATEGORY);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECENT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
	}
	
	public Boolean checkLessonRecent (LessonNode lesson){
		Boolean check = false;
		
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TABLE_RECENT
				+ " WHERE " + KEY_RECENT_TITLE + " ='" + lesson.label + "'";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			check = true;
        }else{
        	check = false;
        }
		//Log.i(cate+"/"+lesson.label, check.toString());
		db.close();
		return check;
	}
	
	public void addNewRecent (LessonNode lesson){
		SQLiteDatabase db = this.getWritableDatabase();
		String queryDelete = "DELETE FROM " + TABLE_RECENT
				+ " WHERE " + KEY_RECENT_TITLE + " = '" + lesson.label + "';";
		db.execSQL(queryDelete);
		
		ContentValues values = new ContentValues();
		values.put(KEY_RECENT_TITLE, lesson.label);
		values.put(KEY_RECENT_PATH, lesson.url);
		Log.i("URL RECENT", lesson.url);
		db.insert(TABLE_RECENT, null, values);
		db.close();
	}
	
	public List<LessonNode> getAllRecent (){
		List<LessonNode> listLesson = new ArrayList<LessonNode>();
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TABLE_RECENT 
				+" ORDER BY " + KEY_RECENT_ID + " DESC"
				+ " LIMIT 0, 20";
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
            do {
            	listLesson.add(new LessonNode(cursor.getString(1), cursor.getString(2))) ;
            } while (cursor.moveToNext());
        }
		db.close();
		return listLesson;
	}
	
	public void deleteRecent (LessonNode lesson){
		SQLiteDatabase db = this.getWritableDatabase();
		String queryDelete = "DELETE FROM " + TABLE_RECENT
				+ " WHERE " + KEY_RECENT_TITLE + " = '" + lesson.label + "';";
		db.execSQL(queryDelete);
		
		db.close();
	}
	
	public void addLessonCategory(String cate, LessonNode lesson){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_CATEGORY_NAME, cate);
		values.put(KEY_CATEGORY_TITLE, lesson.label);
		values.put(KEY_CATEGORY_PATH, lesson.url);
		
		db.insert(TABLE_CATEGORY, null, values);
		db.close();
	}
	
	public void removeLessonCategory (String cate, LessonNode lesson){
		SQLiteDatabase db = this.getWritableDatabase();
		String queryDelete = "DELETE FROM " + TABLE_CATEGORY
				+ " WHERE " + KEY_CATEGORY_NAME + " ='" + cate + "'"
				+ " AND " + KEY_CATEGORY_TITLE + " ='" + lesson.label + "'";
		db.execSQL(queryDelete);
		
		db.close();
	}
	
	public Boolean checkLessonCategory (String cate, LessonNode lesson){
		Boolean check = false;
		
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TABLE_CATEGORY
				+ " WHERE " + KEY_CATEGORY_NAME + " ='" + cate + "'"
				+ " AND " + KEY_CATEGORY_TITLE + " ='" + lesson.label + "'";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			check = true;
        }else{
        	check = false;
        }
		//Log.i(cate+"/"+lesson.label, check.toString());
		db.close();
		return check;
	}
}
